<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> 
<html>
<head>
<title>Getting database metadata</title>
<link rel="stylesheet" href="/cfg/format.css" type="text/css">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
<meta name="keywords" content="Ruby, SQLite, metadata, database, sqlite3">
<meta name="description" content="In this part of the SQLite Ruby tutorial, 
we work with database metadata.">
<meta name="language" content="en">
<meta name="author" content="Jan Bodnar">
<meta name="distribution" content="global">

<script type="text/javascript" src="/lib/jquery.js"></script>
<script type="text/javascript" src="/lib/common.js"></script>

</head>

<body>

<div class="container">

<div id="wide_ad" class="ltow"> 
<script type="text/javascript"><!--
google_ad_client = "pub-9706709751191532";
/* 160x600, August 2011 */
google_ad_slot = "2484182563";
google_ad_width = 160;
google_ad_height = 600;
//-->
</script> 
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> 
</script> 
</div> 

<div class="content">


<a href="/" title="Home">Home</a>&nbsp;
<a href="..">Contents</a>


<h1>Getting database metadata</h1>

<p>
Metadata is information about the data in the database. 
Metadata in SQLite contains information about the tables 
and columns, in which we store data. Number of rows affected 
by an SQL statement is a metadata. Number of rows and columns returned 
in a result set belong to metadata as well.
</p>

<div class="center">
<script type="text/javascript"><!--
google_ad_client = "ca-pub-9706709751191532";
/* top_horizontal */
google_ad_slot = "3327173442";
google_ad_width = 468;
google_ad_height = 60;
//-->
</script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</div>

<p>
Metadata in SQLite can be obtained using the <code>PRAGMA</code> command. 
SQLite objects may have attributes, which are metadata. Finally, we can
also obtain specific metatada from querying the SQLite system 
<code>sqlite_master</code> table. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    pst = db.prepare "SELECT * FROM Cars LIMIT 6"    
    
    puts pst.columns
    puts pst.types
    puts pst.column_count
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    pst.close if pst
    db.close if db
end
</pre>

<p>
In the above example, we get the column names, column types and
the number of columns of a prepared statement. 
</p>

<pre class="explanation">
puts pst.columns
puts pst.types
puts pst.column_count
</pre>

<p>
These three methods return the column names, column types and
the number of columns of a prepared statement. 
</p>

<pre>
$ ./cols_fields.rb
Id
Name
Price
INTEGER
TEXT
INT
3
</pre>

<p>
The output shows three column names: Id, Name and Price. The types
are INTEGER, TEXT and INT. 
</p>

<hr class="btm">

<p>
In the following example, we will find out how many changes has
been done by a particular SQL command.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'


begin
    
    db = SQLite3::Database.new ":memory:"
    
    db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"
    db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"
    db.execute "DELETE FROM Friends WHERE Id IN (3, 4, 5)"
    
    n = db.changes
    puts "There has been #{n} changes"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
We create a Friends table in memory. In the last SQL command, we delete three
rows. We use the <code>changes</code> method to get the number of changes done
by the last SQL operation. 
</p>

<pre class="explanation">
db.execute "DELETE FROM Friends WHERE Id IN (3, 4, 5)"
</pre>

<p>
In this SQL statement, we delete three rows. 
</p>

<pre class="explanation">
n = db.changes
puts "There has been #{n} changes"
</pre>

<p>
We find out the number of changes done by the last SQL
statement. 
</p>

<pre>
$ ./changes.rb
There has been 3 changes
</pre>

<p>
Example output.
</p>


<hr class="btm">

<p>
In the next example, we will find out some data about the
Cars table. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db" 
    
    stm = db.prepare "PRAGMA table_info('Cars')" 
    rs = stm.execute 
    
    rs.each do |row|
        puts row.join "\s"
    end    
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end
</pre>

<p>
In this example, we issue the <code>PRAGMA table_info(tableName)</code> command, 
to get some metadata info about our Cars table. 
</p>

<pre class="explanation">
stm = db.prepare "PRAGMA table_info('Cars')" 
rs = stm.execute 
</pre>

<p>
The <code>PRAGMA table_info(Cars)</code> command returns one row for each 
column in the Cars table. Columns in the result set include the column order number,
column name, data type, whether or not the column can be NULL, 
and the default value for the column.
</p>

<pre class="explanation">
rs.each do |row|
    puts row.join "\s"
end    
</pre>

<p>
We iterate over the result set and print the data. 
</p>

<pre>
$ ./table_info.rb
0 Id INTEGER 0  1
1 Name TEXT 0  0
2 Price INT 0  0
</pre>

<p>
Output of the example. 
</p>


<hr class="btm">

<p>
Next we will print 5 rows from the Cars table with their column names.
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"

    rows = db.execute2 "SELECT * FROM Cars LIMIT 5"
      
    rows.each do |row|
        puts "%3s %-8s %s" % [row[0], row[1], row[2]]
    end    
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
We print five rows of the Cars table to the console. 
Now, we include the names of the columns too. The records are aligned
with the column names. 
</p>

<pre class="explanation">
rows = db.execute2 "SELECT * FROM Cars LIMIT 5"
</pre>

<p>
The <code>execute2</code> method executes the given SQL statement. 
The first row returned is the names of the columns.
</p>

<pre class="explanation">
rows.each do |row|
    puts "%3s %-8s %s" % [row[0], row[1], row[2]]
end 
</pre>

<p>
The data is retrieved, formatted and printed to the terminal.
</p>

<pre>
$ ./column_names.rb
 Id Name     Price
  1 Audi     52642
  2 Mercedes 57127
  3 Skoda    9000
  4 Volvo    29000
  5 Bentley  350000
</pre>

<p>
Output. 
</p>

<hr class="btm">

<p>
In our last example related to the metadata, we will 
list all tables in the test.db database. 
</p>

<pre class="code">
#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    rows = db.execute &lt;&lt;SQL
        SELECT name FROM sqlite_master
            WHERE type='table'
            ORDER BY name;"
SQL
     
    rows.each do |row|
        puts row
    end    
    
rescue SQLite3::Exception => e 
    
    puts "Exception occured"
    puts e
    
ensure
    db.close if db
end
</pre>

<p>
The code example prints all available tables in the current database
to the terminal. 
</p>

<pre class="explanation">
    rows = db.execute &lt;&lt;SQL
        SELECT name FROM sqlite_master
            WHERE type='table'
            ORDER BY name;"
SQL
</pre>

<p>
The table names are retrieved from the <code>sqlite_master</code> table.
</p>

<pre>
$ ./list_tables.rb
Cars
Friends
Images
</pre>

<p>
These were the tables on our system. 
</p>

<p>
In this part of the SQLite Ruby tutorial, we have worked with database
metadata. 
</p>

<div class="botNav, center">
<span class="botNavItem"><a href="/">Home</a></span> ‡ <span class="botNavItem"><a href="..">Contents</a></span> ‡ 
<span class="botNavItem"><a href="#">Top of Page</a></span>
</div>

<div class="footer">
<div class="signature">
<a href="/">ZetCode</a> last modified April 13, 2012  <span class="copyright">&copy; 2007 - 2013 Jan Bodnar</span>
</div>
</div>

</div> <!-- content -->

</div> <!-- container -->

</body>
</html>
